using System;
using System.Data;
using System.Data.SqlClient;

namespace UDS.Management.Data
{
    /// <summary>
    /// public class for executing sql against a database Connection
    /// </summary>
    public static class Execute
    {
        /// <summary>
        /// SqlCommand object factory to create a command.
        /// </summary>
        /// <param name="SqlScript">The sql script to execute</param>
        /// <param name="CommandTimeOut">The time out to use for the command</param>
        /// <returns>A SqlCommand object with an open Connection</returns>
        private static SqlCommand CreateCommand(string SqlScript, int CommandTimeOut)
        {
            SqlCommand command = new SqlCommand();		

            command.CommandText = SqlScript;
            command.CommandTimeout = CommandTimeOut;
            command.CommandType = CommandType.Text;
            
            return command;
        }

        private static SqlCommand CreateStoredProcedureCommand(string ProcedureName, int CommandTimeOut, params SqlParameter[] Parameters)
        {
            SqlCommand Command = new SqlCommand();
            Command.CommandType = CommandType.StoredProcedure;
            Command.CommandTimeout = CommandTimeOut;
            Command.CommandText = ProcedureName;
            if (Parameters != null)
            {
                foreach (SqlParameter Parameter in Parameters)
                {
                    Command.Parameters.Add(Parameter);
                }
            }

            return Command;
        }
        
		/// <summary>
        /// Execute the given sql script against the Connection
        /// </summary>
        /// <param name="Connection">The Connection to use</param>
        /// <param name="SqlScript">The script to execute</param>
        /// <returns>The scalar result</returns>
        public static object ExecuteScalar(SqlConnection Connection, string SqlScript, int CommandTimeOut)
        {
            SqlCommand Command = CreateCommand(SqlScript, CommandTimeOut);
            Command.Connection = Connection;

            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            using (Command)
            {
                return Command.ExecuteScalar();
            }
        }

		/// <summary>
		/// Execute the given sql script against the Connection
		/// </summary>
		/// <param name="Connection">The Connection to use</param>
		/// <param name="SqlScript">The script to execute</param>
		/// <returns>The scalar result</returns>
		public static object ExecuteScalar(SqlTransaction Transaction, string SqlScript, int CommandTimeOut)
		{
			SqlCommand Command = CreateCommand(SqlScript, CommandTimeOut);
			Command.Connection = Transaction.Connection;
			Command.Transaction = Transaction;

			using (Command)
			{
				return Command.ExecuteScalar();
			}
		}

        /// <summary>
        /// Execute the given sql script against the Connection
        /// </summary>
        /// <param name="Connection">SqlConnection</param>
        /// <param name="SqlScript">sql script</param>
        /// <param name="CommandTimeOut">Time out to use</param>
        public static void ExecuteNonQuery(SqlConnection Connection, string SqlScript, int CommandTimeOut)
        {
            SqlCommand Command = CreateCommand(SqlScript, CommandTimeOut);
            Command.Connection = Connection;

            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            using (Command)
            {
                Command.ExecuteNonQuery();
            }
        }

        public static void ExecuteNonQuery(SqlTransaction Transaction, string SqlScript, int CommandTimeOut)
        {
            SqlCommand Command = CreateCommand(SqlScript, CommandTimeOut);
            Command.Connection = Transaction.Connection;
            Command.Transaction = Transaction;

            using (Command)
            {
                Command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Execute the given sql script against the Connection and 
        /// return an SqlDataReader
        /// </summary>
        /// <param name="Connection">SqlConnection</param>
        /// <param name="SqlScript">sql script</param>
        /// <param name="CommandTimeOut">Time out to use</param>
        /// <returns>A data reader to use to enumerate the results</returns>
        public static SqlDataReader ExecuteReader(SqlConnection Connection, string SqlScript, int CommandTimeOut)
        {
            SqlCommand Command = CreateCommand(SqlScript, CommandTimeOut);
            Command.Connection = Connection;

            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            using (Command)
            {
                return Command.ExecuteReader();
            }
        }

        /// <summary>
        /// Execute a stored procedure
        /// </summary>
        /// <param name="Connection">The Connection to use</param>
        /// <param name="ProcedureName">The name of the procedure</param>
        /// <param name="CommandTimeOut">The command timeout</param>
        /// <param name="Parameters">The parameters to use for the procedure</param>
        /// <returns>A data reader to use</returns>
        public static SqlDataReader ExecuteReader(SqlConnection Connection, string ProcedureName, int CommandTimeOut, params SqlParameter[] Parameters) 
        {
            SqlCommand Command = CreateStoredProcedureCommand(ProcedureName, CommandTimeOut, Parameters);
            Command.Connection = Connection;
            
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            using (Command)
            {
                return Command.ExecuteReader();
            }
        }

        public static void ExecuteStoredProcedure(SqlConnection Connection, string ProcedureName, int CommandTimeOut, params SqlParameter[] Parameters)
        {
            SqlCommand Command = CreateStoredProcedureCommand(ProcedureName, CommandTimeOut, Parameters);
            Command.Connection = Connection;

            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            using (Command)
            {
                Command.ExecuteNonQuery();
            }
        }

        public static void ExecuteStoredProcedure(SqlTransaction Transaction, string ProcedureName, int CommandTimeOut, params SqlParameter[] Parameters)
        {
            SqlCommand Command = CreateStoredProcedureCommand(ProcedureName, CommandTimeOut, Parameters);
            Command.Connection = Transaction.Connection;
            Command.Transaction = Transaction;

            if (Transaction.Connection.State != ConnectionState.Open)
            {
                Transaction.Connection.Open();
            }

            using (Command)
            {
                Command.ExecuteNonQuery();
            }
        }
    }
}
